package com.lu.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.lu.entity.Phone;
import com.lu.entity.User;
import com.lu.phone.utils.MobileCodeWS;
import com.lu.phone.utils.MobileCodeWSSoap;
import com.lu.utils.DBTools;

public class PhoneServiceImpl {

	public Phone getPhone(Map<String,Object> map) throws Exception{
		Phone phone = null ;
		StringBuffer sql = new StringBuffer(" select * from phone ");
		int count = 0;
		for(String m : map.keySet()){
			String str = (String)map.get(m);
			if(count==0){
				sql.append(" where "+m+"="+str);
			}else{
				sql.append(" and "+m+"="+str);
			}
			count++;
		}
		System.out.println(sql);
		
		DBTools db = new DBTools();
		ResultSet rs = db.getQuery(sql.toString());
		phone = new Phone();
		if(rs.next()){
			phone.setId(rs.getLong("id"));
			phone.setUserId(rs.getLong("user_id"));
			phone.setProvince(rs.getString("province"));
			phone.setCity(rs.getString("city"));
			phone.setCardType(rs.getString("card_type"));
		}
		db.close();
		return phone;
	}

	public List<Phone> getPhoneList(Map<String, Object> map) throws Exception{
		StringBuffer sql = new StringBuffer(" select * from phone ");
		int count = 0;
		for(String m : map.keySet()){
			String str = (String)map.get(m);
			if(count==0){
				sql.append(" where "+m+"="+str);
			}else{
				sql.append(" and "+m+"="+str);
			}
			count++;
		}
		System.out.println(sql);
		
		DBTools db = new DBTools();
		ResultSet rs = db.getQuery(sql.toString());
		List<Phone> list = new ArrayList<>();
		while(rs.next()){
			Phone phone = new Phone();
			phone.setId(rs.getLong("id"));
			phone.setUserId(rs.getLong("user_id"));
			phone.setProvince(rs.getString("province"));
			phone.setCity(rs.getString("city"));
			phone.setCardType(rs.getString("card_type"));
			list.add(phone);
		}
		db.close();
		return list.size()==0?null:list;
	}

	public Integer save(Phone phone) throws Exception{
		
		String sql = "insert into phone(user_id,province,city,card_type) value (?,?,?,?) ";
		System.out.println(sql);
		DBTools db = new DBTools();
		Connection connection = db.getCon();
		PreparedStatement pre = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//ͨ��Connection����������ֵ��PreparedStatement
		pre.setLong(1,phone.getUserId());
		pre.setString(2,phone.getProvince());
		pre.setString(3,phone.getCity());
		pre.setString(4,phone.getCardType());
		pre.executeUpdate();
		ResultSet rs = pre.getGeneratedKeys();
		Integer id = null;
		if(rs.next()) {
			id = rs.getInt(1);
		}
		db.close();
        return id;
	}
	
	public Integer update(Phone phone) throws Exception{
		String sql = "update phone set province='"+phone.getProvince()+"',"
				+ "city='"+phone.getCity()+"',card_type='"+phone.getCardType()+"' where id="+phone.getId()+" ";
		System.out.println(sql);
		DBTools db = new DBTools();
		int res = db.update(sql);
		db.close();
        return res==0?null:res;
	}

	public Integer remove(Long id) throws Exception{
		String sql = "delete from phone where id="+id+" ";
		System.out.println(sql);
		DBTools db = new DBTools();
		int res = db.update(sql);
		db.close();
        return res==0?null:res;
	}

	public static String getCurrentTimeStr(){
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-MM-DD hh:mm:ss");
		String currentTime = simpleDateFormat.format(new Date());
		return currentTime;
	}
	
}
